''' 滴滴打灰后端应用服务 数据库操作API（BMS管理） '''
import sys,traceback,logging,datetime
import pymysql
from dbutils.pooled_db import PooledDB 
from utils.db_config import mysqlInfo



logger = logging.getLogger('bmsservice')


# 创建数据库连接
def connect_db():
    pool=PooledDB(creator=pymysql,mincached=2, maxcached=5,maxshared=3, maxconnections=6, blocking=True,host=mysqlInfo['host'],port=mysqlInfo['port'],
        user=mysqlInfo['user'],password=mysqlInfo['password'],database=mysqlInfo['database'],charset=mysqlInfo['charset'])
    return pool.connection()


# 查询热门职位列表
def bmsgetPostPageList(hot):
    conn = connect_db()
    cur = conn.cursor()
    postlist = []
    try:
        sql = "select id AS 'id', name AS 'name', pid AS 'pid',hot AS 'hot', sort AS 'sort', status AS 'status', remark AS 'remark' from bms_post_info where hot = %s " 
        cur.execute(sql,(hot))
        for row in cur.fetchall():
            rowdict = {}
            rowdict['id'] = row[0]
            rowdict['name'] = row[1]
            rowdict['pid'] = row[2]
            rowdict['hot'] = row[3]
            rowdict['sort'] = row[4]
            rowdict['status'] = row[5]
            rowdict['remark'] = row[6]
            postlist.append(rowdict)
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return postlist

# 查询职位列表
def getPostList(pid):
    conn = connect_db()
    cur = conn.cursor()
    postlist = []
    try:
        if pid is not None:
            sql = "select id AS 'id', name AS 'name', pid AS 'pid',hot AS 'hot', sort AS 'sort', status AS 'status', remark AS 'remark' from bms_post_info where pid = %s " 
            cur.execute(sql,(pid))
            for row in cur.fetchall():
                rowdict = {}
                rowdict['id'] = row[0]
                rowdict['name'] = row[1]
                rowdict['pid'] = row[2]
                rowdict['hot'] = row[3]
                rowdict['sort'] = row[4]
                rowdict['status'] = row[5]
                rowdict['remark'] = row[6]
                postlist.append(rowdict)
        else:
            # 查询职位父类
            sql = "select id AS 'id', name AS 'name', pid AS 'pid',hot AS 'hot', sort AS 'sort', status AS 'status', remark AS 'remark' from bms_post_info where pid = %s " 
            cur.execute(sql,(0))            
            for row in cur.fetchall():
                rowdict = {}
                tlist = []
                rowdict['id'] = row[0]
                rowdict['name'] = row[1]
                rowdict['pid'] = row[2]
                rowdict['hot'] = row[3]
                rowdict['sort'] = row[4]
                rowdict['status'] = row[5]
                rowdict['remark'] = row[6]
                rowdict['child'] = tlist
                postlist.append(rowdict)
            for i in range(len(postlist)):
                clist = []
                sql = "select id AS 'id', name AS 'name', pid AS 'pid',hot AS 'hot', sort AS 'sort', status AS 'status', remark AS 'remark' from bms_post_info where pid = %s "
                cur.execute(sql,(postlist[i]['id']))
                for row1 in cur.fetchall():
                    cdict = {}
                    tlist = []
                    cdict['id'] = row1[0]
                    cdict['name'] = row1[1]
                    cdict['pid'] = row1[2]
                    cdict['hot'] = row1[3]
                    cdict['sort'] = row1[4]
                    cdict['status'] = row1[5]
                    cdict['remark'] = row1[6]
                    cdict['child'] = tlist
                    clist.append(cdict)
                for j in range(len(clist)):
                    clist1 = []
                    sql = "select id AS 'id', name AS 'name', pid AS 'pid',hot AS 'hot', sort AS 'sort', status AS 'status', remark AS 'remark' from bms_post_info where pid = %s " 
                    cur.execute(sql,(clist[j]['id']))
                    for row2 in cur.fetchall():
                        cdict = {}
                        cdict['id'] = row2[0]
                        cdict['name'] = row2[1]
                        cdict['pid'] = row2[2]
                        cdict['hot'] = row2[3]
                        cdict['sort'] = row2[4]
                        cdict['status'] = row2[5]
                        cdict['remark'] = row2[6]
                        clist1.append(cdict)
                    clist[j]['child'] = clist1
                postlist[i]['child'] = clist
                    
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return postlist

# 查询行业列表
def getIndustryList(pid):
    conn = connect_db()
    cur = conn.cursor()
    industrylist = []
    try:
        if pid is not None:
            sql = "select id AS id, name AS name, pid AS pid, sort AS sort, status AS status, remark AS remark from bms_industry where pid = %s" 
            cur.execute(sql,(pid))
            for row in cur.fetchall():
                rowdict = {}
                rowdict['id'] = row[0]
                rowdict['name'] = row[1]
                rowdict['pid'] = row[2]
                rowdict['sort'] = row[3]
                rowdict['status'] = row[4]
                rowdict['remark'] = row[5]
                industrylist.append(rowdict)
        else:
            # 查询职位父类
            sql = " select id AS id, name AS name, pid AS pid, sort AS sort, status AS status, remark AS remark from bms_industry " 
            cur.execute(sql)            
            for row in cur.fetchall():
                rowdict = {}
                tlist = []
                rowdict['id'] = row[0]
                rowdict['name'] = row[1]
                rowdict['pid'] = row[2]
                rowdict['sort'] = row[3]
                rowdict['status'] = row[4]
                rowdict['remark'] = row[5]
                rowdict['child'] = tlist
                industrylist.append(rowdict)
            for i in range(len(industrylist)):
                clist = []
                sql = " select id AS id, name AS name, pid AS pid, sort AS sort, status AS status, remark AS remark from bms_industry where pid = %s "
                cur.execute(sql,(industrylist[i]['id']))
                for row1 in cur.fetchall():
                    cdict = {}
                    cdict['id'] = row1[0]
                    cdict['name'] = row1[1]
                    cdict['pid'] = row1[2]
                    cdict['sort'] = row1[3]
                    cdict['status'] = row1[4]
                    cdict['remark'] = row1[5]
                    clist.append(cdict)
                industrylist[i]['child'] = clist
                    
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return industrylist

# 查询学校列表
def getSchoolList(param):
    conn = connect_db()
    cur = conn.cursor()
    schoollist = []
    try:
        sql = "select id AS id, cid AS cid, name AS name, level AS level, location AS location, city AS city, is_211 AS is211, is_985 AS is985, is_double_a AS isDoubleA, type_id AS typeId, img AS img, srank AS srank" \
              ", site AS sit, store_id AS storeId, status AS status from bms_school where 1 = 1 "
        if param['keyword'] != '':
            sql += " and name like '%" + param['keyword'] + "%' "
        sql += " LIMIT 0," + param['limit']
        cur.execute(sql)
        for row in cur.fetchall():
            rowdict = {}
            rowdict['id'] = row[0]
            rowdict['cid'] = row[1]
            rowdict['name'] = row[2]
            rowdict['level'] = row[3]
            rowdict['location'] = row[4]
            rowdict['city'] = row[5]
            rowdict['is211'] = row[6]
            rowdict['is985'] = row[7]
            rowdict['isDoubleA'] = row[8]
            rowdict['typeId'] = row[9]
            rowdict['img'] = row[10]
            rowdict['rank'] = row[11]
            rowdict['sit'] = row[12]
            rowdict['storeId'] = row[13]
            rowdict['status'] = row[14]
            schoollist.append(rowdict)
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return schoollist

# 查询福利列表
def getWelfareList(pid):
    conn = connect_db()
    cur = conn.cursor()
    welfarelist = []
    try:
        if pid is not None:
            sql = "select id AS id, welfare_category AS welfareCategory, pid AS pid, name AS name, icon AS icon, descr AS descr, sort AS sort, status AS status, remark AS remark from bms_welfare where pid = %s" 
            cur.execute(sql,(pid))
            for row in cur.fetchall():
                rowdict = {}
                rowdict['id'] = row[0]
                rowdict['welfareCategory'] = row[1]
                rowdict['pid'] = row[2]
                rowdict['name'] = row[3]
                rowdict['icon'] = row[4]
                rowdict['descr'] = row[5]
                rowdict['sort'] = row[6]
                rowdict['status'] = row[7]
                rowdict['remark'] = row[8]
                welfarelist.append(rowdict)
        else:
            # 查询职位父类
            sql = " select id AS id, welfare_category AS welfareCategory, pid AS pid, name AS name, icon AS icon, descr AS descr, sort AS sort, status AS status, remark AS remark from bms_welfare where pid = %s " 
            cur.execute(sql,(0))            
            for row in cur.fetchall():
                rowdict = {}
                tlist = []
                rowdict['id'] = row[0]
                rowdict['welfareCategory'] = row[1]
                rowdict['pid'] = row[2]
                rowdict['name'] = row[3]
                rowdict['icon'] = row[4]
                rowdict['descr'] = row[5]
                rowdict['sort'] = row[6]
                rowdict['status'] = row[7]
                rowdict['remark'] = row[8]
                rowdict['child'] = tlist
                welfarelist.append(rowdict)
            for i in range(len(welfarelist)):
                clist = []
                sql = " select id AS id, welfare_category AS welfareCategory, pid AS pid, name AS name, icon AS icon, descr AS descr, sort AS sort, status AS status, remark AS remark from bms_welfare where pid = %s "
                cur.execute(sql,(industrylist[i]['id']))
                for row1 in cur.fetchall():
                    cdict = {}
                    cdict['id'] = row[0]
                    cdict['welfareCategory'] = row[1]
                    cdict['pid'] = row[2]
                    cdict['name'] = row[3]
                    cdict['icon'] = row[4]
                    cdict['descr'] = row[5]
                    cdict['sort'] = row[6]
                    cdict['status'] = row[7]
                    cdict['remark'] = row[8]
                    clist.append(cdict)
                welfarelist[i]['child'] = clist
                    
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return welfarelist
